# -*- coding: utf-8 -*-
"""
author : heting
date : 2018/6/6 15:21
desc : 
"""
from MyComFiles.MyPublic import *
from MyComFiles.qiYuan.user import User
from MyComFiles.qiYuan.keyMap import keyMap
import time

conn = pymysql.connect(host='172.16.1.90', port=3306, user='lianghua', passwd='xc123', db='qy_approval_prod',
                       charset='UTF8')
engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/qy_temp?charset=utf8', echo=False)

os.chdir('./')
workPath = os.getcwd()
print('workPath: ', os.getcwd())
time_start = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

"""待查询用户身份证号列表"""
user = User()
user.stat_historyOverdueUsers()  #变量
user_id_numList = user.dfhistoryOverdueUsers  #变量

totalusers = len(user_id_numList)

"""生成每月月末-日期列表"""
startDate = '2017-05-01'
dateList = newDateList(startDate)[1:]



def judeOverdue(recordMap,minDays):
    if (minDays >= 1) & (minDays <= 30):
        recordMap['overdue_status'] = 'M1'
    elif (minDays > 30) & (minDays <= 60):
        recordMap['overdue_status'] = 'M2'
    elif (minDays > 60) & (minDays <= 90):
        recordMap['overdue_status'] = 'M3'
    elif (minDays > 90) & (minDays <= 120):
        recordMap['overdue_status'] = 'M4'
    elif (minDays > 120) & (minDays <= 150):
        recordMap['overdue_status'] = 'M5'
    elif (minDays > 150) & (minDays <= 180):
        recordMap['overdue_status'] = 'M6'
    elif (minDays > 181):
        recordMap['overdue_status'] = 'M6+'
    else:
        recordMap['overdue_status'] = ''

"""插入数据前先清空表"""
sql = """delete from history_repay_plan"""
commitSql(sql)

index = 1
for item in user_id_numList:
    user_id_num = item['user_id_num']
    for date in dateList:
        totalList = []
        sql = """
            -- 查询该客户还款计划表明细记录 
            -- status=early_settled 表示已经提前结清
            SELECT
                t.user_name,
                t.user_id_num,
                t.cellphone,
                t.loan_at,
                t.loan_amount,
                t.pay_ben_jin,
                t.already_pay_ben_jin,
                t.pay_loan_interest,
                t.already_pay_loan_interest,
                t.pay_platform_interest,
                t.already_pay_platform_interest,
                t.early_settled_handle_charge,
                t.counted_should_pay,
                t.overdue_days,
                t.total_pay,
                t.already_payed,
                t.total_overdue_amount,
                t.already_pay_overdue_amount,
                t.end_date,
                t.repay_date,
                t.term,
                t.contract_id,
                t.loan_term,
                t.repay_bank_card_num,
                t.repay_bank_name,
                t.overdue_status,
                t.status
            FROM
                qy_repayment_prod.repay_plans t
            where t.user_id_num = '%s'
            and t.repay_date<='%s';
        """ %(user_id_num,date)

        """获取该用户还款计划表明细"""
        df = pdReadSqlNew(sql,conn)

        if len(df):
            recordMapList = df.to_dict("records")

            """遍历修改每一行记录"""
            for recordMap in recordMapList:
                recordMap['queryDate'] = date

                queryDate = pd.to_datetime(date+" 23:59:59")
                status = recordMap['status']
                if status=='payed':
                    pass

                if status=='overdue':
                    """只修改逾期天数 逾期状态"""
                    repay_date = recordMap['repay_date']
                    minDays = (queryDate-repay_date).days
                    print(minDays)
                    recordMap['overdue_days'] = minDays
                    judeOverdue(recordMap, minDays)

                """改逾期天数 逾期状态 是否逾期"""
                if status == 'overdue_payed':
                    repay_date = recordMap['repay_date']
                    end_date = recordMap['end_date']
                    """如果截止月末未还款 或者实际还款时间大于月末日期"""
                    if (str(end_date)=='NaT')|(end_date>queryDate):
                        recordMap['status'] = 'overdue'
                        minDays = (queryDate - repay_date).days
                        recordMap['overdue_days'] = minDays
                        judeOverdue(recordMap, minDays)

                    if end_date<=queryDate:
                        minDays = (end_date - repay_date).days
                        recordMap['overdue_days'] = minDays
                        judeOverdue(recordMap, minDays)
                
                totalList.append(recordMap)

            insertData = DataFrame(totalList)
            insertData['updateTime'] = time_start
            colOrder = ['updateTime','queryDate','user_name', 'user_id_num', 'cellphone', 'loan_at', 'loan_amount', 'pay_ben_jin', 'already_pay_ben_jin', 'pay_loan_interest', 'already_pay_loan_interest', 'pay_platform_interest', 'already_pay_platform_interest', 'early_settled_handle_charge', 'counted_should_pay', 'total_pay', 'already_payed', 'total_overdue_amount', 'already_pay_overdue_amount', 'end_date', 'repay_date', 'term', 'contract_id', 'loan_term', 'repay_bank_card_num', 'repay_bank_name', 'overdue_status', 'overdue_days', 'status']
            insertData[colOrder].to_sql('history_repay_plan', engine,if_exists='append',index=False)

    
    print('to idNo:%s index:%s total:%s' % (user_id_num, index, totalusers))
    index += 1


# saveToExcel('测试数据集', df, workPath)
time_end = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
print('开始时间:', time_start)
print('结束时间:', time_end)